How MySQL Performs Type Conversion When Operators Use Mixed Data Types
MySQL automatically converts (coerces) values to a common type when an expression involves mixed data types. The conversion rules depend on whether the operator is arithmetic, comparison, or string-related.
MySQL converts operands to numbers.
A string is converted to a number by reading digits from the start.
If the string does not start with a digit → it becomes 0.
If both operands look numeric → convert both to numbers.
If either operand is non-numeric → compare them as strings.
Boolean values TRUE/FALSE become 1 and 0.
Operands are converted to strings.
Numbers become text representations.
Any arithmetic with NULL → NULL.
Any comparison with NULL → NULL (except <=>).
<=> (NULL-safe operator) treats NULL as a valid value.
In summary: MySQL converts values based on context—numbers for arithmetic, strings for string operations, and rule-based conversions for comparisons. Understanding these rules helps avoid unexpected results.